INSERT INTO mylock(NAME) VALUES('aaa'); 


/*--------------------------查看当前表加过的锁------------------------------------*/
SHOW OPEN TABLES WHERE in_use > 0;


/*--------------------------为mylock表加S锁（读锁）------------------------------------*/
LOCK TABLE mylock READ;
UNLOCK TABLES;  /*结束锁表状态，终止会话1中的阻塞*/


/*--------------------------为mylock表加S锁（写锁）------------------------------------*/
LOCK TABLE mylock WRITE;
UNLOCK TABLES;  /*结束锁表状态，终止会话1中的阻塞*/


/*--------------------------为mylock表加S锁（读锁）------------------------------------*/
LOCK TABLE actor READ;
UNLOCK TABLES;


/*--------------------------为mylock表加S锁（写锁）------------------------------------*/
LOCK TABLE actor WRITE;
UNLOCK TABLES;


/*--------------------------不能为mylock表加行级排他锁X（写锁），因为它是MyISAM引擎------------------------------------*/
BEGIN;
SELECT NAME FROM mylock WHERE id = 1 FOR UPDATE;
COMMIT;


/*-----------事务中的update语句会获取行级排他锁X------------*/
BEGIN;
UPDATE actor SET first_name = '获取行级排他锁X' WHERE actor_id = 6;
COMMIT;


/*---------------------添加表锁后，尝试操作当前表-----------------------*/
SELECT * FROM mylock LOCK IN SHARE MODE;


/*--------------------------测试是否可读取其他表------------------------------------*/
SELECT * FROM actor;	/*无法同时读取其他表*/


/*--------------------------为actor表的某些记录加上行级共享锁S（读锁）------------------------------------*/
BEGIN;
SELECT first_name FROM actor WHERE actor_id = 6 LOCK IN SHARE MODE;
COMMIT;	/*提交事务，结束事务*/


/*--------------------------为actor表的某些记录加上行级排他锁X（写锁）------------------------------------*/
BEGIN;
SELECT first_name FROM actor WHERE actor_id = 6 FOR UPDATE;  /*悲观锁*/
COMMIT;	/*提交事务，结束事务，终止会话2中的阻塞状态*/


/*-------------查询某条其他记录时，尝试获取当前行的行级共享锁S-------------------*/
BEGIN;
SELECT first_name FROM actor WHERE actor_id = 3 LOCK IN SHARE MODE;
COMMIT;